﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for DBAcess
/// </summary>
public class DBAccess
{
    private static string sqlString;
    private static SqlConnection con;
    private static DataSet ds;
    private static string query;
	public DBAccess()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public static int checkAcount(string acount, string pass)
    {
        int result = 0;
        con = MyConnection.get();
        string sqlQuery = "";
        if (pass.Equals(""))
            sqlQuery = "select Role from Acount where Acount.IDAcount = '" + acount + "'";
        else
            sqlQuery = "select Role from Acount where Acount.IDAcount = '" + acount + "' AND Acount.Pass ='" + pass + "'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        //con.Open();
        try
        {
            try
            {
                result = (byte)command.ExecuteScalar();
            }
            catch (NullReferenceException)
            {
                result = -1;
            }
        }
        catch (SqlException)
        {
            return 0;
        }
        finally
        {
            con.Close();
        }
        return result;
    }

   
    public static int CreateAcount(AcountDTO acount)
    {
        int result = 0;
        con = MyConnection.get();
        sqlString = "CreateAcount";        
        SqlCommand command = new SqlCommand(sqlString, con);
        command.CommandType = CommandType.StoredProcedure;
        try
        {
            //con.Open();
            //command.Parameters.Add("@idAcount", SqlDbType.Int).Value = acount;
            command.Parameters.Add("@FirstName", SqlDbType.NVarChar, 100).Value = acount.FirstName;
            command.Parameters.Add("@LastName", SqlDbType.NVarChar, 100).Value = acount.LastName;
            command.Parameters.Add("@BirthDay", SqlDbType.Date).Value = acount.BirthDay;
			command.Parameters.Add("@Male", SqlDbType.Bit).Value = acount.Male;
			command.Parameters.Add("@Pass", SqlDbType.NVarChar,100).Value = acount.Pass;
			command.Parameters.Add("@Phone", SqlDbType.NVarChar,50).Value = acount.Phone;
			command.Parameters.Add("@Email", SqlDbType.NVarChar,50).Value = acount.Email;
			command.Parameters.Add("@Website", SqlDbType.NVarChar,50).Value = acount.Website;
			command.Parameters.Add("@CMND", SqlDbType.NVarChar,50).Value = acount.CMND;
			command.Parameters.Add("@Role", SqlDbType.TinyInt).Value = acount.Role;
			command.Parameters.Add("@BankID", SqlDbType.Int).Value = acount.BankID;
			command.Parameters.Add("@BankNumber", SqlDbType.NVarChar,20).Value = acount.BankNumber;
			command.Parameters.Add("@LastLogin", SqlDbType.DateTime).Value = DateTime.Now;
			command.Parameters.Add("@Latitude", SqlDbType.Float).Value = acount.Latitude;
			command.Parameters.Add("@Longitude", SqlDbType.Float).Value = acount.Longitude;
            command.Parameters.Add("@AreaID", SqlDbType.Int).Value = acount.HomeTown;
            command.Parameters.Add("@IDAcount", SqlDbType.NVarChar, 32).Value = acount.IDAcount;
            command.Parameters.Add("@Address", SqlDbType.NVarChar, 350).Value = acount.Address;
            command.Parameters.Add("@Avarta", SqlDbType.NVarChar, 250).Value = acount.Avarta;
            result = (int)command.ExecuteNonQuery();
            con.Close();
            createLogRevews(acount.IDAcount,acount.LogReviews.TopReviews);
            sendMail("Admin",acount.IDAcount, 1, "Đăng kí thành công", "Bạn đã rờ thành thành viên của website ");
            
        }
        catch
        {
            result = 0;
            con.Close();
        }       
        return result;
    }

    private static void sendMail(string senderAcountID, string RecipierAcount, int typeMail, string header, string content)
    {
        int result = 0;
        con = MyConnection.get();
        sqlString = "SendMail";
        SqlCommand command = new SqlCommand(sqlString, con);
        command.CommandType = CommandType.StoredProcedure;
        try
        {
            command.Parameters.Add("@TypeID", SqlDbType.TinyInt).Value = typeMail;
            command.Parameters.Add("@Header", SqlDbType.NVarChar, 150).Value = header;
            command.Parameters.Add("@Context", SqlDbType.NVarChar, 500).Value = content;
            command.Parameters.Add("@AcountID", SqlDbType.NVarChar, 32).Value = RecipierAcount;
            command.Parameters.Add("@SenderAcount", SqlDbType.NVarChar, 32).Value = senderAcountID;
            result = (int)command.ExecuteScalar();
        }
        catch
        {
            result = 0;
        }
        finally
        {
            con.Close();
        }
    }

    private static void createLogRevews(string idAcount, KeyValuePair<int, int>[] keyValuePair)
    {
        int result = 0;
        con = MyConnection.get();
        sqlString = "CreatelogSoftWare";        
        SqlCommand command = new SqlCommand(sqlString, con);
        command.CommandType = CommandType.StoredProcedure;
        try
        {           

            command.Parameters.Add("@AcountID", SqlDbType.NVarChar,32).Value = idAcount;
            command.Parameters.Add("@CatalogID1", SqlDbType.Int).Value = keyValuePair[0].Key;
            command.Parameters.Add("@Value1", SqlDbType.Int).Value = keyValuePair[0].Value;
            command.Parameters.Add("@CatalogID2", SqlDbType.Int).Value = keyValuePair[1].Key;
            command.Parameters.Add("@Value2", SqlDbType.Int).Value = keyValuePair[1].Value;
            command.Parameters.Add("@CatalogID3", SqlDbType.Int).Value = keyValuePair[2].Key;
            command.Parameters.Add("@Value3", SqlDbType.Int).Value = keyValuePair[2].Value;
            result = (int)command.ExecuteNonQuery();
        }
        catch
        {
            result = 0;          
        }
        finally
        {
            con.Close();
        }
    }
    public static AcountDTO getAcount(string acount)
    {
        ds = new DataSet();
        con = MyConnection.get();
        query = "SELECT * FROM ViewAcount WHERE ViewAcount.IDAcount = '" + acount + "'";
        SqlCommand command = new SqlCommand(query, con);
        command.CommandType = CommandType.Text;
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            AcountDTO a = new AcountDTO();
            a.Acount = acount;
            a.IDAcount = ds.Tables[0].Rows[0]["IDAcount"].ToString();
            a.FirstName = ds.Tables[0].Rows[0]["FirstName"].ToString();
            a.LastName = ds.Tables[0].Rows[0]["LastName"].ToString();
            a.BirthDay = (DateTime)ds.Tables[0].Rows[0]["BirthDay"];
            a.Male = (bool)ds.Tables[0].Rows[0]["Male"];
            a.Pass = ds.Tables[0].Rows[0]["Pass"].ToString();
            a.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
            a.Email = ds.Tables[0].Rows[0]["Email"].ToString();
            a.Website = ds.Tables[0].Rows[0]["Website"].ToString();
            a.CMND = ds.Tables[0].Rows[0]["CMND"].ToString();
            a.Role = (byte)ds.Tables[0].Rows[0]["Role"];
            a.BankID = (int)ds.Tables[0].Rows[0]["BankID"];
            a.BankNumber = ds.Tables[0].Rows[0]["BankNumber"].ToString();
            a.LastLogin = ds.Tables[0].Rows[0]["LastLogin"].ToString();
            a.Latitude = (double)ds.Tables[0].Rows[0]["Latitude"];
            a.Longitude = (double)ds.Tables[0].Rows[0]["Longitude"];
            a.HomeTown = (int)ds.Tables[0].Rows[0]["AreaID"];           
            a.Address = ds.Tables[0].Rows[0]["Address"].ToString();
            a.Avarta = ds.Tables[0].Rows[0]["Avarta"].ToString();
            a.LogReviews.add((int)ds.Tables[0].Rows[0]["CatalogID1"],(int)ds.Tables[0].Rows[0]["Value1"]);
            a.LogReviews.add((int)ds.Tables[0].Rows[0]["CatalogID2"], (int)ds.Tables[0].Rows[0]["Value2"]);
            a.LogReviews.add((int)ds.Tables[0].Rows[0]["CatalogID3"], (int)ds.Tables[0].Rows[0]["Value3"]);
            return a;
        }
        catch
        {
            return null;
        }
        finally
        {
            con.Close();
        }
    }

    internal static List<MailDTO> getListOutbox(string idAcount)
    {
        ds = new DataSet();
        con = MyConnection.get();
        query = "SELECT * FROM OutMail WHERE OutMail.SenderAcount = " + idAcount;
        SqlCommand command = new SqlCommand(query, con);
        command.CommandType = CommandType.Text;
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            List<MailDTO> mails = new List<MailDTO>();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                MailDTO mail = new MailDTO();
                mail.IsInbox = false;
                mail.IDmail = (int)row["idAcount"];
                mail.TypeID = (int)row["TypeID"];
                mail.Header = row["Header"].ToString();
                mail.Context = row["Context"].ToString();
                mail.Date = (DateTime)row["Date"];
                mail.SenderAcountID = idAcount;
            }
            return null;
        }
        catch
        {
            return null;
        }
        finally
        {
            con.Close();
        }
    }

    internal static List<MailDTO> getListInbox(string idAcount)
    {
        ds = new DataSet();
        con = MyConnection.get();
        query = "SELECT * FROM ViewInboxMail WHERE ViewInboxMail.AcountID = '" + idAcount+"'";
        SqlCommand command = new SqlCommand(query, con);
        command.CommandType = CommandType.Text;
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            List<MailDTO> mails = new List<MailDTO>();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                MailDTO mail = new MailDTO();
                mail.IsInbox = true;
                mail.IDmail = (int)row["idAcount"];
                mail.TypeID = (int)row["TypeID"];
                mail.Header =  row["Header"].ToString();
                mail.Context = row["Context"].ToString();
                mail.Date = (DateTime)row["Date"];
                mail.AcountID = (int)row["AcountID"];
                mail.Type= row["TypeName"].ToString();
                mail.Check = (bool)row["Checked"];
                mail.NameSender = row["Acount"].ToString();
               
            }
            return null;
        }
        catch
        {
            return null;
        }
        finally
        {
            con.Close();
        }
    }

    internal static AcountDTO getAcount(int acountID)
    {    
        ds = new DataSet();
        con = MyConnection.get();
        query = "SELECT * FROM ACOUNT WHERE ViewAcount.idAcount = "+acountID;
        SqlCommand command = new SqlCommand(query, con);
        command.CommandType = CommandType.Text;
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            AcountDTO a = new AcountDTO();           
            a.IDAcount = ds.Tables[0].Rows[0]["idAcount"].ToString();
            a.FirstName = ds.Tables[0].Rows[0]["FirstName"].ToString();
            a.LastName = ds.Tables[0].Rows[0]["LastName"].ToString();
            a.BirthDay = (DateTime)ds.Tables[0].Rows[0]["BirthDay"];
            a.Male = (bool)ds.Tables[0].Rows[0]["Male"];
            a.Pass = ds.Tables[0].Rows[0]["Pass"].ToString();
            a.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
            a.Email = ds.Tables[0].Rows[0]["Email"].ToString();
            a.Website = ds.Tables[0].Rows[0]["Website"].ToString();
            a.CMND = ds.Tables[0].Rows[0]["CMND"].ToString();
            a.Role = (byte)ds.Tables[0].Rows[0]["Role"];
            a.BankID = (int)ds.Tables[0].Rows[0]["BankID"];
            a.BankNumber = ds.Tables[0].Rows[0]["BankNumber"].ToString();
            a.LastName = ds.Tables[0].Rows[0]["LastLogin"].ToString();
            a.Latitude = (float)ds.Tables[0].Rows[0]["Latitude"];
            a.Longitude = (float)ds.Tables[0].Rows[0]["Longitude"];
            a.HomeTown = (int)ds.Tables[0].Rows[0]["AreaID"];
            a.Acount = ds.Tables[0].Rows[0]["Acount"].ToString();
            a.Address = ds.Tables[0].Rows[0]["Address"].ToString();
            a.Avarta = ds.Tables[0].Rows[0]["Avarta"].ToString();
            a.LogReviews.Reviews.Add((int)ds.Tables[0].Rows[0]["CatalogID1"], (int)ds.Tables[0].Rows[0]["Value1"]);
            a.LogReviews.Reviews.Add((int)ds.Tables[0].Rows[0]["CatalogID2"], (int)ds.Tables[0].Rows[0]["Value2"]);
            a.LogReviews.Reviews.Add((int)ds.Tables[0].Rows[0]["CatalogID3"], (int)ds.Tables[0].Rows[0]["Value3"]);
            return a;
        }
        catch
        {
            return null;
        }
        finally
        {
            con.Close();
        }
    }
}